use CreativeSystems;
GO

ALTER PROC sp_OutstandingBalances
(
	@DueDateStartRange DATETIME,
	@DueDateEndRange DATETIME,
	@ClientName VARCHAR(MAX)= null
)
AS

--table Variables
DECLARE @InvoiceTotalPayments TABLE (InvoiceID int, PaymentTotal money);
DECLARE @InvoiceTotal TABLE (InvoiceID int, InvoiceTotal money);

-- Total Payments made per invoice
INSERT INTO @InvoiceTotalPayments(InvoiceID, PaymentTotal)
SELECT i.InvoiceID, SUM(p.Amount)
FROM Invoice i
INNER JOIN Payment p ON i.InvoiceID = p.InvoiceID
WHERE i.DueDate >= @DueDateStartRange
AND i.DueDate <= @DueDateEndRange
GROUP BY i.InvoiceID;

--invoice total
INSERT INTO @InvoiceTotal(InvoiceID, InvoiceTotal)
SELECT i.InvoiceID, SUM(item.Price * item.Qty)
FROM Invoice i
INNER JOIN InvoiceItem item ON item.InvoiceID = i.InvoiceID
WHERE i.DueDate >= @DueDateStartRange
AND i.DueDate <= @DueDateEndRange
Group By i.InvoiceID;

--main report query
SELECT DueDate
,FirstName + ' ' + LastName "Client"
,Title "Event"
,ISNULL(itp.PaymentTotal, 0.00) "Total Payments"
,ISNULL(it.InvoiceTotal, 0.00) "Invoice Total"
,ISNULL((it.InvoiceTotal - itp.PaymentTotal), it.InvoiceTotal) "Remaining Balance"
FROM Invoice i
INNER JOIN [Event] e on e.EventID = i.EventID
INNER JOIN Client c on e.ClientID = c.ClientID
LEFT OUTER JOIN @InvoiceTotalPayments itp ON itp.InvoiceID = i.InvoiceID
LEFT OUTER JOIN	@InvoiceTotal it ON it.InvoiceID = i.InvoiceID
WHERE i.DueDate >= @DueDateStartRange
AND i.DueDate <= @DueDateEndRange
--AND (c.FirstName + ' ' + c.LastName IN (@ClientName))
AND c.FirstName + ' ' + c.LastName = COALESCE(@ClientName, c.FirstName + ' ' + c.LastName)
AND ((it.InvoiceTotal - itp.PaymentTotal > 0) OR (itp.PaymentTotal IS NULL AND it.InvoiceTotal > 0))
ORDER BY DueDate;